<?php

require_once("Xiachufang/util/Logger.php");

/**
 * 数据库访问异常.
 *
 */
class DataAccessException extends Exception {

	public function __construct($message, $code = 0) {
		parent::__construct($message, $code);
	}

}

/**
 * 转义字符串内容,并在字符串前后加上单引号,其它类型值原样返回.
 * 只在内部使用.
 *
 * @param mixed $value
 * @return mixed
 */
function _mysqlEscapeAll($conn,$value) {
	if(is_bool($value)) {
		if ($value===true) {
			return 1;
		}
		else {
			return 0;
		}
	}
	if ($value===NULL) {
		return 'NULL';
	}
	if (''===$value) {
		return "''";
	}
	if (is_string($value)) {
		return "'".mysqli_real_escape_string($conn,$value)."'";
	}
	return $value;
}

/**
 * 回滚没有提交的事务,关闭连接.
 *
 * @param Mysql $mysql
 */
function _clearConnection($mysql) {
	if ($mysql->txBegined===true) {
		$mysql->rollback();
	}
	$mysql->destroy();
}

class Mysql {
	private static $instances = array();
	var $conn = NULL;
	var $txBegined = false;
	var $database = false;
	var $newdb = false;
	private static $log = NULL;

	//从已经建立的mysql连接创建一个连接包装器
	function __construct($mysql=NULL,$newdb=NULL) {
		if (self::$log == NULL) {
			self::$log = Logger::getLogger("Mysql");
		}

		if (!$mysql){
			return;
		}
		$this->conn = $mysql->conn;
		$this->txBegined = $mysql->txBegined;
		$this->database = $mysql->database;
		if ($newdb){
			$this->newdb = $newdb;
		} else {
			$this->newdb = $mysql->newdb;
		}
		//    	$this->log = $mysql->log;
	}

	public function checkDb(){
		if ($this->newdb){
			$this->chooseDb($this->newdb);
		}
	}
    /**
     * 建立到mysql的连接,如连接成功,返回Mysql实例,如连接失败,返回false.
     * @return Mysql Mysql instance or false. 
     */
    public static function connect($host,$user,$password,$database) {

    	if (self::$log == NULL) {
    		self::$log = Logger::getLogger("Mysql");
    	}

    	if ($database==NULL) {
    		$conn = @new mysqli($host,$user,$password);
    	}
    	else {
    		$conn = @new mysqli($host,$user,$password,$database);
    	}
    	if (mysqli_connect_errno()) {
    		self::$log->fatal("can't connect to $host/$database($user:$password):".mysqli_connect_error());
    		return false;
    	}
    	@mysqli_query($conn,"SET NAMES 'utf8'");
    	$mysql = new Mysql();
    	$mysql->conn = $conn;

    	if ($database !=NULL) {
    		$mysql->database = $database;
    		$mysql->newdb = $database;
    	}

    	register_shutdown_function('_clearConnection',$mysql);
    	Mysql::$instances[] = $mysql;
    	return $mysql;
    }

    /**
     * 选择一个数据库.
     *
     * @param string $databaseName 数据库名.
     */
    function chooseDb($databaseName) {
    	if (@mysqli_select_db($this->conn,$databaseName)==false) {
    		$error = mysqli_error($this->conn);
    		self::$log->fatal("select database to $database_name failed:$error");
    		throw new DataAccessException("切换数据库失败:$error");
    	}
    	$this->database = $databaseName;
    }

    /**
     * 断开连接.
     *
     */
    function disconnect() {
    	if ($this->conn!=NULL) {
    		if ($this->txBegined===true) {
    			self::$log->fatal("transaction not commit");
    			throw new DataAccessException("有事务没有提交");
    		}
    		if (self::$log->isDebugEnabled()) {
    			self::$log->debug("disconnect database connection");
    		}
    		@mysqli_close($this->conn);
    		$this->conn = NULL;
    	}
    }

    /**
     * 关闭连接,disconnect方法的别名.
     *
     */
    function close() {
    	$this->disconnect();
    }

    function destroy() {
    	$this->close();
    }

    /**
     * 关闭所有mysql连接.
     */
    public static function destroyAll() {
    	foreach (Mysql::$instances as $mysql) {
    		$mysql->destroy();
    	}
    	Mysql::$instances = array();
    }

    /**
     * 创建可复用的sql查询,可使用?作占位符.
     *
     * @param string $sql
     * @return _MysqlStatement 可对该对象多次调用execute方法.
     */
    function prepare($sql) {
    	$this->checkDb();
    	return new _MysqlStatement($this,$sql);
    }

    /**
     * 执行一条查询语句.
     *
     * @param string $sql
     * @param array $parameters
     * @return _MysqlResultSet 返回一个结果集.
     */
    function query($sql,$parameters=array()) {
    	$this->checkDb();
    	if ($parameters==NULL || count($parameters)==0) {
    		return new _MysqlResultSet($this,$sql);
    	} else {
    		$stmt = $this->prepare($sql);
    		return $stmt->execute($parameters);
    	}
    }

    /**
     * query方法的别名.
     *
     * @param string $sql
     * @return _MysqlResultSet 返回一个结果集.
     */
    function execute($sql,$parameters=NULL) {
    	$this->checkDb();
    	return $this->query($sql,$parameters);
    }

    /**
     * @return array 如果没有查询到数据,返回NULL.
     */
    function queryUnique($sql,$parameters=array()) {
    	$this->checkDb();
    	$executeSql = $sql;
    	$conn = $this->conn;
    	if (count($parameters)>0) {
    		$executeSql = buildSql($conn,$sql,$parameters);
    	}

    	if (self::$log->isDebugEnabled()) {
    		self::$log->start();
    		self::$log->setMethod("queryUnique");
    	}
    	$result = $conn->query($executeSql);
    	if (self::$log->isDebugEnabled()) {
    		self::$log->debug_end("sql=$executeSql");
    		self::$log->clearMethod();
    	}
    	if ($result==false) {
    		$error = mysqli_error($conn);
    		self::$log->fatal("execute sql($executeSql) failed:$error","sql error:".$error);
    		throw new DataAccessException("数据库操作失败");
    	}
    	if ($result->num_rows==0) {
    		$result->free_result();
    		return NULL;
    	}
    	if ($result->num_rows>1) {
    		$result->free_result();
    		throw new DataAccessException("结果不唯一");
    	}
    	$data = $result->fetch_assoc();
    	$result->free_result();
    	return $data;
    }

    /**
     * 查询单一结果.
     * @return mixed 查询结果的第一行每一列,没有查询到结果返回NULL.
     */
    function querySingle($sql,$parameters=array()) {
    	$this->checkDb();
    	$data = $this->queryUnique($sql,$parameters);
    	if ($data!=NULL) {
    		if (count($data)==0) {
    			return NULL;
    		}
    		return array_shift($data);
    	}
    	else {
    		return NULL;
    	}
    }

    /**
     * 查询sql的结果数.
     *
     * @param string $sql 必须是一条select语句.
     * @return int 数量.
     */
    function totalNumQuery($sql,$parameters=array()) {
    	$this->checkDb();
    	if (preg_match("/^\s*select\s/i",$sql)==0) {
    		throw new DataAccessException("无效的select语句:$sql");
    	}
    	$sql = preg_replace("/^(\s*select\s+(distinct)?)(.*?)(\s*from\s.*?)(order\s*by[^\)]*|group\s*by[^\)]*)?$/is","\${1} count(*) _count_ \${4}",$sql);
    	$stmt = $this->prepare($sql);
    	$rs = $stmt->execute($parameters);
    	if ($rs->getRows()!=1) {
    		throw new DataAccessException("查询结果集数量失败");
    	}
    	$row = $rs->next();
    	return $row['_count_'];
    }

    /**
     * 分页查询.
     *
     * @param string $sql 要执行的查询语句.
     * @param int $page 第几页,从0开始.
     * @param int $pageSize 每页的大小.
     * @return _MysqlResultSet 返回一个结果集.
     */
    function pagedQuery($sql,$page,$pageSize,$parameters=array()) {
    	$this->checkDb();
    	$sql .= " limit ".($page*$pageSize).",$pageSize";
    	$stmt = $this->prepare($sql);
    	return $stmt->execute($parameters);
    }

    /**
     * 完整分页查询.
     *
     * @param string $sql
     * @param int $page
     * @param int $pageSize
     * @param array $parameters
     * @param int $total
     * @return PageResult result
     */
    function fullPagedQuery($sql,$page,$pageSize,$parameters=array(),$total=NULL) {
    	$this->checkDb();
    	if ($total===NULL) {
    		$total = $this->totalNumQuery($sql,$parameters);
    	}
    	$rs = $this->pagedQuery($sql,$page,$pageSize,$parameters);
    	return PageResult::build($total,$page,$pageSize,$rs->asArray());
    }

    /**
     * 插入一条记录.
     *
     * @param array $obj 要插入的记录内容,忽略key值为int类型的keyvalue对.
     * @param string $table 表名.
     * @param bool $returnId 是否返回自动生成的主键
     * @return bool 插入成功返回true,失败返回false.
     */
    function insert($obj,$table,$returnId=false){
    	$sql = "insert into $table(";
    	$append = " values(";
    	foreach ($obj as $name=>$value) {
    		if (!is_int($name)) {
    			$sql .= $name.",";
    			$append .= _mysqlEscapeAll($this->conn,$value).",";
    		}
    	}
    	$sql = substr($sql,0,strlen($sql)-1);
    	$append = substr($append,0,strlen($append)-1);
    	$sql .= ") ";
    	$sql.=$append.")";
    	$result = new _MysqlResultSet($this,$sql);
    	if($result->isSuccess()) {
    		if ($returnId) {
    			return $this->getLastInsertId();
    		}
    		else {
    			return true;
    		}
    	}
    	else {
    		return false;
    	}
    }

    /**
     * 批量插入数据.
     * @param array $data 二维数组
     * @param string $table 表名
     * @param bool	$isDuplicatesUpdate 冲突是否更新之
     * @return bool 插入成功返回true,失败返回false.
     */
    function batchInsert($data,$table,$isDuplicatesUpdate = false) {
    	if (!is_array($data)) {
    		self::$log->fatal("批量插入数据参数错误：期望传入的是数组");
    		throw new Exception("data must be a array");
    	}
    	$sql = $isDuplicatesUpdate?"insert into $table(":"insert ignore into $table(";
    	$firstItem = $data[0];
    	$names = array_keys($firstItem);
    	$sql .= implode(",",$names);
    	$sql .= ") values";
    	$parts = array();
    	foreach ($data as $item) {
    		$append = "(";
    		foreach ($names as $name) {
    			$value = $item[$name];
    			$append .= _mysqlEscapeAll($this->conn,$value).",";
    		}
    		$append = substr($append,0,strlen($append)-1);
    		$append .= "),";
    		$sql .= $append;
    	}
    	$sql = substr($sql,0,strlen($sql)-1);

    	if($isDuplicatesUpdate) //进行update
    	{
    		$update = " ON DUPLICATE KEY UPDATE ";
    		$valueArray = array();
    		foreach($names as $name)
    		{
    			$valueArray[] = "$name=values($name)"; 
    		}
    		$update .= implode(',',$valueArray); 
    		$sql.=$update;	
    	}
		
    	$result = new _MysqlResultSet($this,$sql);
    	if($result->isSuccess()) {
    		return true;
    	}
    	else {
    		self::$log->fatal("批量插入数据参数错误：期望传入的是数组");
    		return false;
    	}
    }

    function replace($obj,$table) {
    	$this->checkDb();
    	$sql = "replace into $table(";
    	$append = " values(";
    	foreach ($obj as $name=>$value) {
    		if (!is_int($name)) {
    			$sql .= $name.",";
    			$append .= _mysqlEscapeAll($this->conn,$value).",";
    		}
    	}
    	$sql = substr($sql,0,strlen($sql)-1);
    	$append = substr($append,0,strlen($append)-1);
    	$sql .= ") ";
    	$sql.=$append.")";
    	$result = new _MysqlResultSet($this,$sql);
    	if($result->isSuccess()) {
    		return true;
    	}
    	else {
    		return false;
    	}
    }

    function getLastInsertId() {
    	$this->checkDb();
    	$id = mysqli_insert_id($this->conn);
    	return $id;
    }

    /**
     * 执行一条删除语句.
     * @param string $sql 必须以delete开头.
     */
    function delete($sql) {
    	$this->checkDb();
    	$result = new _MysqlResultSet($this,$sql);
    	if ($result->isSuccess()==false) {
    		return false;
    	}
    	else {
    		return $result->getRows();
    	}
    }

    /**
     * 更新一条记录.
     *
     * @param array $obj 要更新记录的内容.
     * @param string $table 表名
     * @param string $condition 更新条件.
     * @return mixed 更新成功返回影响的记录数,失败返回false.
     */
    function update($obj,$table,$condition) {
    	$this->checkDb();
    	$sql = "update $table set ";
    	foreach ($obj as $name=>$value) {
    		if (!is_int($name)) {
    			$sql .= " $name="._mysqlEscapeAll($this->conn,$value).",";
    		}
    	}
    	$sql = substr($sql,0,strlen($sql)-1);
    	if (preg_match("/^\s*where\s+/i",$condition)==0) {
    		$sql .= " where ";
    	}
    	$sql .= $condition;
    	$result = new _MysqlResultSet($this,$sql);
    	if ($result->isSuccess()==false) {
    		return false;
    	}
    	else {
    		return $result->getRows();
    	}
    }

    //transaction control
    /**
     * 开始事务.
     */
    function beginTx() {
    	$this->checkDb();
    	if (self::$log->isDebugEnabled()) {
    		self::$log->debug("begin transaction");
    	}
    	if ($this->txBegined) {
    		self::$log->fatal("不需要重复开启事务");
    		throw new DataAccessException("不需要重复开启事务");
    	}
    	$this->execute("start transaction");
    	$this->txBegined = true;
    }

    /**
     * 回滚事务.
     *
     */
    function rollback() {
    	$this->checkDb();
    	if (self::$log->isDebugEnabled()) {
    		self::$log->debug("rollback transaction");
    	}
    	$this->execute("rollback");
    	$this->txBegined = false;
    }

    /**
     * 提交事务.
     *
     */
    function commit() {
    	$this->checkDb();
    	if (self::$log->isDebugEnabled()) {
    		self::$log->debug("commit transaction");
    	}
    	if ($this->txBegined!=true) {
    		self::$log->fatal("没有开启事务,不需要提交");
    		throw new DataAccessException("没有开启事务,不需要提交");
    	}
    	$this->execute("commit");
    	$this->txBegined = false;
    }

}

class PageResult {

	/**@var int*/
	var $total;

	/**@var int*/
	var $totalPage;

	/**@var int*/
	var $page;

	/**@var int*/
	var $pageSize;

	/**@var int*/
	var $currentPageSize;

	/**@var int*/
	var $start;

	/**@var int*/
	var $end;

	/**@var array*/
	var $data = array();

	/**@var array*/
	var $info = array();

    /**
     * 构造分页结果.
     *
     * @param int $total
     * @param int $page
     * @param int $pageSize
     * @param array $data
     * @return PageResult result
     */
    public static  function build($total,$page,$pageSize,&$data) {
    	$pageResult = new PageResult();
    	$pageResult->currentPageSize = count($data);
    	if ($total < $pageResult->currentPageSize){
    		$total = $pageResult->currentPageSize;
    	}
    	$pageResult->total = $total;
    	$totalPage = (int)(($total+$pageSize-1)/$pageSize);
    	$pageResult->totalPage = $totalPage;
    	$pageResult->page = $page;
    	$pageResult->pageSize = $pageSize;
    	$pageResult->start = $page*$pageSize;
    	$pageResult->end = ($page+1)*$pageSize;
    	if ($page==$totalPage-1) {
    		$pageResult->end = $total;
    	}
    	if ($page<$totalPage) {
    		$pageResult->data = $data;
    	}
    	else {
    		$pageResult->start = 0;
    		$pageResult->end = 0;
    	}
    	if ($pageResult->end>$pageResult->total) {
    		$pageResult->end = $pageResult->total;
    	}

    	return $pageResult;
    }

    public function render($urlFormat) {
    	if ($this->totalPage <= 1) return "";
		$ret = "";
		$page = $this->page + 1;
		if ($this->total == 0) return $ret;
		if ($this->totalPage > 1) {
			if ($page > 1) $ret .= '&nbsp;<a href="'.$this->getUrl($urlFormat,1).'">首页</a>&nbsp;<a href="'.
				$this->getUrl($urlFormat,$page-1).'">上一页</a>';
			else $ret .= '&nbsp;<span>首页</span>&nbsp;<span>上一页</span>';
		}
		$n = $this->getNumbers(4);
		for($i = $n[0]; $i <= $n[1]; $i++) {
			if ($page == $i) $ret .= '&nbsp;<span class="c">'.$i.'</span>';
			else $ret .= '&nbsp;<a href="'.$this->getUrl($urlFormat,$i).'">'.$i.'</a>';
		}
		if ($this->totalPage > 1) {
			if ($page < $this->totalPage) $ret .= '&nbsp;<a href="'.$this->getUrl($urlFormat,$page+1).
				'">下一页</a>&nbsp;<a href="'.$this->getUrl($urlFormat,$this->totalPage).'">尾页</a>';
			else $ret .= '&nbsp;<span>下一页</span>&nbsp;<span>尾页</span>';
		}
		return $ret;
    }
    
    /**
     * 获取指定页次的地址
     *
     * @param unknown_type $urlFormat
     * @param unknown_type $p
     * @return unknown
     */
    private function getUrl($urlFormat, $p) {
    	return str_replace("{0}", $p, $urlFormat);
    }
    /**
     * 获取需要显示的页码
     *
     * @param unknown_type $t
     */
    private function getNumbers($t) {
    	$page = $this->page + 1;
		if ($page <= $this->totalPage - $page) {
			$min = max(array($page - $t, 1));
			$d = 2*$t - ($page - $min);
			$max = min(array($page + $d, $this->totalPage));
		} else {
			$max = min(array($page + $t, $this->totalPage));
			$d = 2*$t - ($max - $page);
			$min = max(array($page - $d, 1));
		}
		return array($min, $max);
    }
}

function buildSql($conn,$sql,$parameters) {
	$ss = explode("?",$sql);
	$executeSql = '';
	$size = count($parameters);
	$i = 0;
	foreach ($parameters as $value) {
		$executeSql.=$ss[$i];
		$escapeValue = _mysqlEscapeAll($conn,$value);
		$executeSql.= $escapeValue;
		$i++;
	}
	if ($sql[strlen($sql)-1]!='?') {
		$executeSql .= $ss[count($ss)-1];
	}
	return $executeSql;
}


/**
 * 不要直接使用该类,使用Mysql类的prepare方法获取.
 *
 */
class _MysqlStatement {
	private static $log = NULL;
	var $mysql;
	var $sql;
	var $replaceCount;

	function __construct($mysql,$sql) {
		if (self::$log == NULL) {
			self::$log = Logger::getLogger("_MysqlStatement");
		}
		$this->replaceCount = substr_count($sql,'?');
		$this->mysql = $mysql;
		$this->sql = $sql;
		if (self::$log->isDebugEnabled()) {
			self::$log->debug("sql=$sql");
		}
	}

	function execute($parameters) {
		$size = count($parameters);
		if ($size!=$this->replaceCount) {
			self::$log->fatal("need ".$this->replaceCount." parameters,actual $size");
			throw new DataAccessException("期待".$this->replaceCount."个参数,实际给出了".$size."个");
		}
		$executeSql = buildSql($this->mysql->conn,$this->sql,$parameters);
		if (self::$log->isDebugEnabled()) {
			self::$log->debug("sql=".$executeSql);
		}
		return new _MysqlResultSet($this->mysql,$executeSql);
	}

	function close() {
		//TODO
	}
}

/**
 * 不要直接使用该类,使用Mysql的query方法或_MysqlStatement的execute方法获取.
 *
 */
class _MysqlResultSet {
	/**@var LogBase */
	private static $log = NULL;
	/**@var Mysql */
	var $mysql;
	var $sql;
	var $success;
	var $rows;
	var $currentRow = 0;
	var $resource;
	var $closed = false;

	function __construct($mysql,$sql) {
		$mysql->checkDb();
		if (self::$log==NULL) {
			self::$log = Logger::getLogger("_MysqlResultSet");
		}
		$sql = preg_replace("/;$/","",$sql);
		$this->mysql = $mysql;
		$this->sql = $sql;
		if (self::$log->isDebugEnabled()) {
			self::$log->start();
		}
		$result = @mysqli_query($this->mysql->conn,$this->sql);
		if (self::$log->isDebugEnabled()) {
			self::$log->debug_end("sql=$sql");
		}
		if ($result===false) {
			$error = mysqli_error($mysql->conn);
			self::$log->fatal("execute sql($sql) failed:$error","sql error".$error);
			throw new DataAccessException("数据库操作失败");
		}
		if ($result===true) {
			$this->success = true;
			$this->rows = @mysqli_affected_rows($this->mysql->conn);
			if (self::$log->isDebugEnabled()) {
				self::$log->debug("affected rows:".$this->rows);
			}
		}
		else {
			$this->success = $result!==false;
			$this->rows = @mysqli_num_rows($result);
			$this->resource = $result;
			if (self::$log->isDebugEnabled()) {
				self::$log->debug("result rows:".$this->rows);
			}
		}
	}

    /**
     * 查询是否成功.
     *
     * @return bool
     */
    function isSuccess() {
    	return $this->success;
    }

    /**
     * 对于insert,update,delete,返回影响的行数,对于select,返回结果数.
     *
     * @return int
     */
    function getRows() {
    	return $this->rows;
    }

    /**
     * 判断是否存在下一行记录.
     *
     * @return bool 存在更多记录返回true,否则返回false.
     */
    function hasNext() {
    	if (!isset($this->resource)) {
    		return false;
    	}
    	return $this->currentRow<$this->rows;
    }

    /**
     * 取得下一行记录.
     *
     * @return array 返回key=>value数组,如不存在下一行记录返回false.
     */
    function next() {
    	if(!$this->hasNext()) {
    		return false;
    	}
    	$result = @mysqli_fetch_assoc($this->resource);
    	if ($result===false) {
    		$this->close();
    		$this->closed = true;
    		return false;
    	}
    	$this->currentRow++;
    	if ($this->currentRow==$this->rows) {
    		$this->close();
    	}
    	return $result;
    }

    /**
     * @return array
     */
    function asArray() {
    	$result = array();
    	while($this->hasNext()) {
    		$result[] = $this->next();
    	}
    	return $result;
    }

    /**
     * get first column as a array.
     *
     * @return array array
     */
    function asArray1(){
    	$result = array();
    	while($this->hasNext()) {
    		$result[] =array_shift($this->next() );
    	}
    	return $result;
    }
    /**
     * 返回第一个行第一个元素
     * @a [r]
     */
    function asObject(){
    	$result="";
    	if($this->hasNext()){
    		$result=array_shift($this->next());
    	}
    	return $result;
    }
    function close() {
    	if ($this->closed==false && isset($this->resource)) {
    		@mysqli_free_result($this->resource);
    		$this->closed = true;
    		if (self::$log->isDebugEnabled()) {
    			self::$log->debug("close resultset");
    		}
    	}
    }

}

?>